-- This procedure takes a table name and returns a string containing a comma delimited
-- list of column names from the table.  If the optional parameter InIncludeKeys is set
-- to Y, it will include the key columns in the list, by default, it will not. Also, by
-- specifying columns names to skip, the user can remove columns that would otherwise
-- have appeared in the list
--
-- Parameters:
--   IN - InTableName - the name fo the table for which the column list is desired
--   OUT - OutColumnList - the list of columns, comma delimited
--   IN - InIncludeKeys - Optional, Y (yes) or N (no) to include key columns in the list
--   IN - SkipColumnName1-5 - Optional, allows the user to specify up to 5 columns to
--        leave out of the list.
--
-- Copyright 2005 Advanced Solutions Intl., All Rights Reserved

CREATE  PROCEDURE amsp_GetTableColumnList
    @InTableName sysname,
    @OutColumnList nvarchar(4000) OUTPUT,
    @InIncludeKeys char(1) = 'N',
    @SkipColumnName1 sysname = '',
    @SkipColumnName2 sysname = '',
    @SkipColumnName3 sysname = '',
    @SkipColumnName4 sysname = '',
    @SkipColumnName5 sysname = '' AS
  DECLARE GetColumns CURSOR LOCAL READ_ONLY FOR
  SELECT b.name, (
         SELECT 1
           FROM sysindexes c, sysindexkeys d
          WHERE b.id = c.id
            AND c.id = d.id
            AND c.status & 2048 = 2048
            AND c.indid = d.indid
            AND b.colid = d.colid) AS PK
    FROM sysobjects a, syscolumns b
   WHERE a.id = b.id
     AND a.name = @InTableName
   ORDER BY b.colorder
  BEGIN
    DECLARE
    @ColumnName sysname,
    @ColumnStatus int
    
    SET @OutColumnList = ''
    OPEN GetColumns
     
    FETCH NEXT FROM GetColumns
     INTO @ColumnName, @ColumnStatus
    
    WHILE @@FETCH_STATUS = 0
        BEGIN
        IF (@ColumnStatus IS NULL OR @ColumnStatus <> 1 OR
           (@ColumnStatus = 1 AND @InIncludeKeys = 'Y')) AND
           @ColumnName NOT IN (@SkipColumnName1, @SkipColumnName2, @SkipColumnName3, @SkipColumnName4, @SkipColumnName5)
          SET @OutColumnList = @OutColumnList + @ColumnName + ','
  
        FETCH NEXT FROM GetColumns
         INTO @ColumnName, @ColumnStatus
      END
      
    CLOSE GetColumns
    DEALLOCATE GetColumns
    
    SET @OutColumnList = Left(@OutColumnList,Len(@OutColumnList) - 1)
  END

GO
GRANT EXECUTE ON  [dbo].[amsp_GetTableColumnList] TO [IMIS]
GO